import logging
from datetime import datetime

from flask import Blueprint, jsonify, request

from mysql_tool.mysql_tool import MySQLTool

auxiliary_fixtures_bp = Blueprint('auxiliary_fixtures', __name__)

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def get_auxiliary_data(mysql_tool, wo_code):
    """查询锡膏信息"""
    auxiliary_sql = """
    SELECT
      sfcdatmes_am_log.WoCode,
      sfcdatmes_am_log.ReelID,
      sfcdatmes_am_log.Step,
      sfcdatmes_am_vstorage.SupplierID,
      sfcdatmes_am_vstorage.BatchID,
      sfcdatmes_am_log.CreateTime,
      sfcdatmes_am_log.Creator
    FROM
      sfcdatmes_am_log
      LEFT JOIN sfcdatmes_am_vstorage ON sfcdatmes_am_log.ReelID = sfcdatmes_am_vstorage.ReelID
    WHERE
      sfcdatmes_am_log.WoCode = %s
      AND sfcdatmes_am_log.Step = '发料'
    ORDER BY
      sfcdatmes_am_log.CreateTime DESC
    """
    auxiliary_results = mysql_tool.execute_query(auxiliary_sql, (wo_code,))

    auxiliary_data = []
    for row in auxiliary_results:
        create_time = row[5]
        if isinstance(create_time, datetime):
            create_time = create_time.isoformat()

        item = {
            "woCode": row[0],
            "reelID": row[1],
            "step": row[2],
            "supplierID": row[3],
            "batchID": row[4],
            "createTime": create_time,
            "creator": row[6]
        }
        auxiliary_data.append(item)

    return auxiliary_data


def get_fixture_data(mysql_tool, wo_code):
    """查询治具信息"""
    fixture_sql = """
    SELECT
      sfcdatmes_fix_log.wocode,
      sfcdatmes_fix_log.ReelID,
      sfcdatmes_fix_log.Recipient,
      sfcdatmes_fix_vstorage.UseQty,
      sfcdatmes_fix_vstorage.Thickness,
      sfcdatmes_fix_vstorage.MateID,
      sfcdatmes_fix_log.Step
    FROM
      sfcdatmes_fix_log
      LEFT JOIN sfcdatmes_fix_vstorage ON sfcdatmes_fix_log.ReelID = sfcdatmes_fix_vstorage.ReelID
    WHERE
      sfcdatmes_fix_log.wocode = %s
      AND sfcdatmes_fix_log.Step = "上线"
    ORDER BY
      sfcdatmes_fix_vstorage.MateID DESC
    """
    fixtures = mysql_tool.execute_query(fixture_sql, (wo_code,))

    fixture_list = []
    for row in fixtures:
        item = {
            "woCode": row[0],
            "reelID": row[1],
            "recipient": row[2],
            "useQty": row[3],
            "thickness": row[4],
            "mateID": row[5],
            "step": row[6]
        }
        fixture_list.append(item)

    return fixture_list


@auxiliary_fixtures_bp.route('/query_af', methods=['POST'])
def query_auxiliary_fixtures():
    """综合查询接口，通过工单编码同时查询锡膏和治具(钢网、刮刀)信息"""
    try:
        # 获取请求中的JSON数据
        request_data = request.get_json()
        if not request_data:
            return jsonify({"success": False, "message": "请求数据不能为空"}), 400

        # 获取工单编码
        wo_code = request_data.get('wo_code')
        if not wo_code:
            return jsonify({"success": False, "message": "工单编码不能为空"}), 400

        # 创建MySQL工具实例
        mysql_tool = MySQLTool('172.27.32.57', 'root', 'zc123456', 'a50', '9988')

        # 确保连接数据库
        if not mysql_tool.connect():
            return jsonify({"success": False, "message": "数据库连接失败"}), 500

        try:
            # 查询锡膏信息
            auxiliary_data = get_auxiliary_data(mysql_tool, wo_code)

            # 查询治具信息
            fixture_list = get_fixture_data(mysql_tool, wo_code)

            # 返回合并后的结果
            return jsonify({
                "success": True,
                "data": {
                    "auxiliary": auxiliary_data,
                    "fixtures": fixture_list
                }
            })

        finally:
            # 确保断开数据库连接
            mysql_tool.disconnect()

    except Exception as e:
        logger.error(f"查询材料信息失败: {str(e)}")
        return jsonify({
            "success": False,
            "message": f"查询失败: {str(e)}"
        }), 500

@auxiliary_fixtures_bp.route('/queryUsageRecord', methods=['POST'])
def query_usage_record():
    """查询钢网使用记录接口，通过钢网编号查询其使用历史记录"""
    try:
        # 获取请求中的JSON数据
        request_data = request.get_json()
        if not request_data:
            return jsonify({"success": False, "message": "请求数据不能为空"}), 400

        # 获取钢网编码
        stencil_sn = request_data.get('stencilSn')
        if not stencil_sn:
            return jsonify({"success": False, "message": "钢网编码不能为空"}), 400

        # 创建MySQL工具实例
        mysql_tool = MySQLTool()

        # 确保连接数据库
        if not mysql_tool.connect():
            return jsonify({"success": False, "message": "数据库连接失败"}), 500

        try:
            # 查询钢网使用记录
            usage_sql = """
                        SELECT
                            id,
                            stencil_sn,
                            checkout_time,
                            checkout_person,
                            appearance_check,
                            top_left_tension,
                            top_right_tension,
                            center_tension,
                            bottom_left_tension,
                            bottom_right_tension,
                            offline_cleaning,
                            work_order,
                            production_qty,
                            usage_count_per_wo,
                            cumulative_usage_count
                        FROM
                            stencil_usage_record
                        WHERE
                            stencil_sn = %s
                        ORDER BY
                            checkout_time DESC \
                        """

            usage_results = mysql_tool.execute_query(usage_sql, (stencil_sn,))

            # 处理查询结果
            usage_records = []
            for row in usage_results:
                checkout_time = row[2]

                # 处理日期时间格式
                if isinstance(checkout_time, datetime):
                    checkout_time = checkout_time.isoformat()

                record = {
                    "id": row[0],
                    "stencilSn": row[1],
                    "checkoutTime": checkout_time,
                    "checkoutPerson": row[3],
                    "appearanceCheck": row[4],
                    "topLeftTension": row[5],
                    "topRightTension": row[6],
                    "centerTension": row[7],
                    "bottomLeftTension": row[8],
                    "bottomRightTension": row[9],
                    "offlineCleaning": row[10],
                    "workOrder": row[11],
                    "productionQty": row[12],
                    "usageCountPerWo": row[13],
                    "cumulativeUsageCount": row[14]
                }
                usage_records.append(record)

            # 返回结果
            return jsonify({
                "success": True,
                "data": usage_records
            })

        finally:
            # 确保断开数据库连接
            mysql_tool.disconnect()

    except Exception as e:
        logger.error(f"查询钢网使用记录失败: {str(e)}")
        return jsonify({
            "success": False,
            "message": f"查询失败: {str(e)}"
        }), 500
